
create table #ZZMD00(
	UnitOfMeasurePrimary	CHAR(2),
	UnitOfMeasureAsInput	CHAR(6),
	PriceOverrideCode	CHAR(1),
	OrderTypeCode	CHAR(2),
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	ItemNumberID	DECIMAL(19, 0),
	InvoiceDateID	DECIMAL(19, 0),
	GLDateID	DECIMAL(19, 0),
	DocumentType	CHAR(2),
	DocumentNumber	INTEGER,
	CustomerNumberID	DECIMAL(19, 0),
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT,
	WJXBFS3	FLOAT,
	WJXBFS4	FLOAT,
	WJXBFS5	FLOAT,
	WJXBFS6	FLOAT,
	WJXBFS7	FLOAT)

insert into #ZZMD00
select	a11.UnitOfMeasurePrimary  UnitOfMeasurePrimary,
	a11.UnitOfMeasureAsInput  UnitOfMeasureAsInput,
	a12.PriceOverrideCode  PriceOverrideCode,
	a11.OrderTypeCode  OrderTypeCode,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ItemNumberID  ItemNumberID,
	a11.InvoiceDateID  InvoiceDateID,
	a11.GLDateID  GLDateID,
	a12.DocumentType  DocumentType,
	a12.DocumentNumber  DocumentNumber,
	a11.CustomerNumberID  CustomerNumberID,
	sum(a11.QuantityShipped)  WJXBFS1,
	sum(a11.ExtendedPrice)  WJXBFS2,
	sum((a11.LB * a11.QuantityShipped))  WJXBFS3,
	sum((a11.LT * a11.QuantityShipped))  WJXBFS4,
	sum(a11.ExtendedCost)  WJXBFS5,
	sum((a11.CS * a11.QuantityShipped))  WJXBFS6,
	sum((a11.EA * a11.QuantityShipped))  WJXBFS7
from	Sales_Star_Facts	a11
	join	Sales_Star_FactsExt	a12
	  on 	(a11.FactsExtID = a12.FactsExtID)
	join	Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
	join	Common_Star_Dates	a14
	  on 	(a11.InvoiceDateID = a14.CompanyDateID)
where	(a11.TransactionType = '2'
 and a11.OrderTypeCode in ('CO', 'SF', 'SD', 'SO', 'SU')
 and a14.Dates between '2011-01-01' and '2011-12-31'
 and a13.ShiptoNumber in (6300016, 6300017, 6844438, 6296206)
 and a11.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE'))
group by	a11.UnitOfMeasurePrimary,
	a11.UnitOfMeasureAsInput,
	a12.PriceOverrideCode,
	a11.OrderTypeCode,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ItemNumberID,
	a11.InvoiceDateID,
	a11.GLDateID,
	a12.DocumentType,
	a12.DocumentNumber,
	a11.CustomerNumberID OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD01(
	ShiptoNumber	INTEGER,
	ShiptoNumber0	INTEGER,
	OrderTypeCode	CHAR(2),
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	ItemNumberID	DECIMAL(19, 0),
	WJXBFS1	FLOAT)

insert into #ZZMD01
select	a13.ShiptoNumber  ShiptoNumber,
	a13.ShiptoNumber  ShiptoNumber0,
	a11.OrderTypeCode  OrderTypeCode,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ItemNumberID  ItemNumberID,
	sum(a11.PL_UnitPrice)  WJXBFS1
from	Sales_Star_Price_Facts	a11
	join	Sales_Star_PriceLedger	a12
	  on 	(a11.LineNumber = a12.LineNumber and
	a11.OrderNumber = a12.OrderNumber and
	a11.OrderNumberDocumentCompany = a12.OrderNumberDocumentCompany and
	a11.OrderTypeCode = a12.OrderTypeCode and
	a11.PriceAdjustmentKeyID = a12.PriceAdjustmentKeyID and
	a11.SourceID = a12.SourceID)
	join	Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
	join	Sales_Star_Facts	a14
	  on 	(a11.InvoiceDateID = a14.InvoiceDateID and
	a11.ItemNumberID = a14.ItemNumberID and
	a11.LineNumber = a14.LineNumber and
	a11.OrderNumber = a14.OrderNumber and
	a11.OrderNumberDocumentCompany = a14.OrderNumberDocumentCompany and
	a11.OrderTypeCode = a14.OrderTypeCode and
	a11.ShiptoNumber = a14.ShiptoNumber and
	a11.SourceID = a14.SourceID)
where	(a11.OrderTypeCode in ('CO', 'SF', 'SD', 'SO', 'SU')
 and a13.ShiptoNumber in (6300016, 6300017, 6844438, 6296206)
 and a14.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE')
 and a12.SequenceNumber between 902 and 998)
group by	a13.ShiptoNumber,
	a13.ShiptoNumber,
	a11.OrderTypeCode,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ItemNumberID OPTION (MERGE JOIN, LOOP JOIN)

select	distinct a12.ShiptoNumber  ShiptoNumber,
	a12.ShiptoName  ShiptoName,
	a12.ShiptoNumber  ShiptoNumber0,
	a17.AlphaName  AlphaName,
	pa11.InvoiceDateID  InvoiceDateID,
	a15.Dates  Dates,
	pa11.OrderNumber  OrderNumber,
	pa11.LineNumber  LineNumber,
	pa11.OrderTypeCode  OrderTypeCode,
	a14.L1_Code  L1_Code,
	a14.L1  UserDefinedCodesValues,
	a14.ItemNumber02  ItemNumber02,
	a14.Description01  Description010,
	pa11.UnitOfMeasurePrimary  UnitOfMeasurePrimary,
	pa11.UnitOfMeasureAsInput  UnitOfMeasureAsInput,
	pa11.DocumentNumber  DocumentNumber,
	pa11.DocumentType  DocumentType,
	pa11.PriceOverrideCode  PriceOverrideCode,
	a14.L2_surrogatekey  L2_Code,
	a14.L2  UserDefinedCodesValues0,
	a15.CalendarMonth  CalendarMonth,
	pa11.GLDateID  GLDateID,
	a16.Dates  Dates0,
	a15.FiscalMonth  FiscalMonth,
	a15.FiscalMonthName  FiscalMonthName,
	a12.Category03Code  Category03Code,
	a12.Category03  Category03,
	pa11.CustomerNumberID  CustomerNumberID,
	LEFT(a12.CustomerNumberID, (LEN(a12.CustomerNumberID) - 10))  CustCol_4,
	pa11.ItemNumberID  ItemNumberID,
	a12.Category04Code  Category04Code,
	a12.Category04  Category04,
	pa11.WJXBFS1  WJXBFS1,
	pa11.WJXBFS2  WJXBFS2,
	pa11.WJXBFS3  WJXBFS3,
	pa11.WJXBFS4  WJXBFS4,
	(ISNULL(pa11.WJXBFS2, 0) + ISNULL((pa13.WJXBFS1 * pa11.WJXBFS1), 0))  NetSales,
	ISNULL((pa13.WJXBFS1 * pa11.WJXBFS1), 0)  WJXBFS5,
	pa11.WJXBFS5  WJXBFS6,
	pa11.WJXBFS6  WJXBFS7,
	pa11.WJXBFS7  WJXBFS8
from	#ZZMD00	pa11
	join	Sales_Star_Customer	a12
	  on 	(pa11.CustomerNumberID = a12.CustomerNumberID)
	left outer join	#ZZMD01	pa13
	  on 	(a12.ShiptoNumber = pa13.ShiptoNumber and
	a12.ShiptoNumber = pa13.ShiptoNumber0 and
	pa11.ItemNumberID = pa13.ItemNumberID and
	pa11.LineNumber = pa13.LineNumber and
	pa11.OrderNumber = pa13.OrderNumber and
	pa11.OrderTypeCode = pa13.OrderTypeCode)
	join	Common_Star_Item	a14
	  on 	(pa11.ItemNumberID = a14.ItemNumberID)
	join	Common_Star_Dates	a15
	  on 	(pa11.InvoiceDateID = a15.CompanyDateID)
	join	Common_Star_Dates	a16
	  on 	(pa11.GLDateID = a16.CompanyDateID)
	join	Common_Star_AddressBook	a17
	  on 	(a12.ShiptoNumber = a17.AddressNumber)


drop table #ZZMD00

drop table #ZZMD01
